import { join } from "path";
import { app, ipcMain } from "electron";
import knex from "knex";

/**
 * @type {knex.Knex}
 */
let db;

export async function insertSpc(spcData) {
  const dataset = [...spcData.data];
  for (const i in dataset) {
    dataset[i].uuid = spcData.uuid;
  }
  const info = { ...spcData };
  delete info.data;
  const trx = await db.transaction();
  try {
    await trx("spc_part").insert(info);
    await trx("spc_data").insert(dataset);
    await trx.commit();
    return true;
  } catch (e) {
    console.log("error", e);
    await trx.rollback();
    return false;
  }
}

export async function getSpc(
  process = undefined,
  start = undefined,
  end = undefined,
  workType = undefined,
  finishAt = undefined,
  reported = undefined
) {
  const trx = await db.transaction();
  try {
    let query = trx("spc_part").join("spc_data", "spc_part.uuid", "spc_data.uuid").select("*");
    if (process) {
      query = query.where("spc_part.process", "=", process);
    }
    if (start) {
      query = query.where("spc_part.create_at", ">=", +start);
    }
    if (end) {
      query = query.where("spc_part.create_at", "<", +end);
    }
    if (workType !== null && workType !== undefined) {
      if (workType instanceof Array) {
        query = query.whereIn("spc_part.work_type", workType);
      } else {
        query = query.where("spc_part.work_type", "=", workType);
      }
    }
    if (finishAt !== null && finishAt !== undefined) {
      query = query.where("spc_part.finish_at", finishAt);
    }
    if (reported !== undefined && reported !== null) {
      query = query.where("spc_part.reported", "=", reported);
    }
    const data = await query;
    await trx.commit();
    const resMap = {};
    for (const d of data) {
      if (resMap[d.uuid] === undefined) {
        resMap[d.uuid] = {
          uuid: d.uuid,
          process: d.process,
          model: d.model,
          part: d.part_type,
          work_type: d.work_type,
          line_id: d.line_id,
          operator: d.operator,
          operator_name: d.operator_name,
          identity: d.identity,
          reported: d.reported,
          create_at: d.create_at,
          finish_at: d.finish_at,
          data: [
            {
              type: d.type,
              value: d.value,
              qualified: d.qualified
            }
          ]
        };
      } else {
        resMap[d.uuid].data.push({ type: d.type, value: d.value, qualified: d.qualified });
      }
    }
    const result = [];
    for (const item in resMap) {
      result.push(resMap[item]);
    }
    return result;
  } catch (e) {
    await trx.rollback();
    console.log("error", e);
  }
}

export async function tagReportedSpc(uuids) {
  const trx = await db.transaction();
  try {
    await trx("spc_part").whereIn("spc_part.uuid", uuids).update({ reported: true });
    await trx.commit();
    return true;
  } catch (e) {
    await trx.rollback();
    console.log(e);
    return false;
  }
}

export async function delByUuid(uuid) {
  const trx = await db.transaction();
  try {
    await trx("spc_data").where("uuid", "=", uuid).del();
    await trx("spc_part").where("uuid", "=", uuid).del();
    await trx.commit();
    return true;
  } catch (e) {
    await trx.rollback();
    console.log("error", e);
    return false;
  }
}

export async function delByUuids(uuids) {
  const trx = await db.transaction();
  try {
    await trx("spc_data").whereIn("uuid", uuids).del();
    await trx("spc_part").whereIn("uuid", uuids).del();
    await trx.commit();
    return true;
  } catch (e) {
    await trx.rollback();
    console.log("error", e);
    return false;
  }
}

export async function deleteExpireSpc() {
  const threeDaysAgo = new Date();
  threeDaysAgo.setDate(threeDaysAgo.getDate() - 2);
  threeDaysAgo.setHours(8, 30, 0, 0);
  const aWeekAgo = new Date();
  aWeekAgo.setDate(threeDaysAgo.getDate() - 7);
  aWeekAgo.setHours(8, 30, 0, 0);
  const trx = await db.transaction();
  try {
    const data = await trx("spc_part")
      .select("uuid")
      .where("spc_part.reported", true)
      .andWhere("spc_part.create_at", "<", +threeDaysAgo)
      .orWhere("spc_part.create_at", "<", +threeDaysAgo);
    const uuids = data.map((d) => d.uuid);
    if (data.length && data.length > 0) {
      await trx("spc_data").whereIn("uuid", uuids).del();
      await trx("spc_part").whereIn("uuid", uuids).del();
    }
    // 执行commit才会关闭会话
    await trx.commit();
    return true;
  } catch (e) {
    await trx.rollback();
    console.log("error", e);
    return false;
  }
}

export async function insertAnomaly(data) {
  const trx = await db.transaction();
  try {
    await trx("anomaly").insert({
      workCenterId: data.workCenterId,
      workCenterName: data.workCenterName,
      content: data.content,
      processId: data.processId,
      processName: data.processName,
      time: data.time,
      reporter: data.reporter,
      create_at: +data.create_at,
      reported: false
    });
    await trx.commit();
    return true;
  } catch (e) {
    console.log(e);
    return false;
  } finally {
    await trx.commit();
  }
}

export async function finishBatch() {
  const trx = await db.transaction();
  try {
    await trx("spc_part").whereNull("finish_at").update({ finish_at: +new Date() });
  } catch (e) {
    console.log(e);
    return false;
  } finally {
    await trx.commit();
  }
}

export async function getAnomaly(
  workCenterId = undefined,
  processId = undefined,
  start = undefined,
  end = undefined,
  reported = undefined
) {
  const trx = await db.transaction();
  try {
    let query = trx("anomaly").select("*");
    if (workCenterId) {
      query = query.where("anomaly.workCenterId", "=", workCenterId);
    }
    if (processId) {
      query = query.where("anomaly.processId", "=", processId);
    }
    if (start) {
      query = query.where("anomaly.create_at", ">=", +start);
    }
    if (end) {
      query = query.where("anomaly.create_at", "<", +end);
    }
    if (reported !== undefined && reported !== null) {
      query = query.where("anomaly.reported", "=", reported);
    }
    const data = await query;
    await trx.commit();
    return data;
  } catch (e) {
    await trx.rollback();
    console.log("error", e);
  }
}

export async function tagReportedAnomaly(id) {
  const trx = await db.transaction();
  try {
    await trx("anomaly").where("anomaly.id", "=", id).update({ reported: true });
    await trx.commit();
    return true;
  } catch (e) {
    await trx.rollback();
    console.log(e);
    return false;
  }
}

export async function deleteExpireAnomaly() {
  const threeDaysAgo = new Date();
  threeDaysAgo.setDate(threeDaysAgo.getDate() - 2);
  threeDaysAgo.setHours(8, 30, 0, 0);
  const trx = await db.transaction();
  try {
    await trx("anomaly")
      .where("anomaly.reported", true)
      .where("anomaly.create_at", "<", +threeDaysAgo)
      .del();
    // 执行commit才会关闭会话
    await trx.commit();
    return true;
  } catch (e) {
    await trx.rollback();
    console.log("error", e);
    return false;
  }
}

export function initDBIpc() {
  db = knex({
    client: "sqlite3",
    useNullAsDefault: true,
    connection: { filename: join(app.getPath("userData"), "./data.db") }
  });

  db.schema
    .hasTable("spc_part")
    .then((exist) => {
      // if (exist) return;
      // 下一个版本可注释该代码替换为上面代码 start ->
      if (exist) {
        db.schema.hasColumn("spc_part", "finish").then((exist) => {
          if (exist) {
            return db.schema.table("spc_part", (table) => {
              table.dropColumn("finish");
            });
          }
        });
        db.schema.hasColumn("spc_part", "finish_at").then((exist) => {
          if (!exist) {
            return db.schema.table("spc_part", (table) => {
              table
                .bigInteger("finish_at")
                .nullable()
                .comment("零件批次是否完成，小批和全检时有效");
            });
          }
        });
        return;
      }
      // <- end
      return db.schema.createTable("spc_part", (table) => {
        table.string("uuid").primary().comment("零件唯一编码");
        table.string("process").comment("工序名称");
        table.string("model").comment("零件型号");
        table.string("part_type").comment("零件类型");
        table
          .integer("work_type")
          .comment("作业类型 0-过程点检 1-量仪点检 2-全检 3-首件送检 4-过程送检");
        table.string("line_id").comment("产线编号");
        table.string("operator").comment("操作者工号");
        table.string("operator_name").comment("操作者姓名");
        table.integer("identity").comment("作业身份 0-普通员工 1-班组长 2-巡检员");
        table.boolean("reported").comment("是否已上报");
        table.bigInteger("finish_at").nullable().comment("零件批次是否完成，小批和全检时有效");
        table.bigInteger("create_at").unsigned();
        table.index("create_at", "ix_create_at");
        table.index("finish_at", "ix_finish_at");
        table.index("reported", "ix_reported");
      });
    })
    .catch((err) => {
      console.log(err);
    });
  db.schema
    .hasTable("spc_data")
    .then((exist) => {
      if (exist) return;
      return db.schema.createTable("spc_data", (table) => {
        table.increments("id").primary().unsigned();
        table.string("uuid").comment("零件唯一编码");
        table.foreign("uuid").references("spc_part.uuid").onDelete("CASCADE");
        table.string("type").comment("数据类型，内径、平面度等");
        table.float("value").comment("数据值");
        table.boolean("qualified").comment("合格判定");
      });
    })
    .catch((err) => {
      console.log(err);
    });
  db.schema
    .hasTable("anomaly")
    .then((exist) => {
      if (exist) return;
      return db.schema.createTable("anomaly", (table) => {
        table.increments("id").primary().unsigned();
        table.string("workCenterId").comment("产线编号");
        table.string("workCenterName").comment("产线名称");
        table.string("processId").comment("工序id");
        table.string("processName").comment("工序名称");
        table.string("content").comment("异常信息");
        table.string("time").comment("异常发生时段");
        table.string("reporter").comment("异常上报者工号");
        table.boolean("reported").comment("是否已上报");
        table.bigInteger("create_at").unsigned();
        table.index("create_at", "ix_a_create_at");
        table.index("reported", "ix_a_reported");
      });
    })
    .catch((err) => {
      console.log(err);
    });
  ipcMain.handle("spc-insert", async (e, data) => {
    return await insertSpc(data);
  });
  ipcMain.handle(
    "spc-get-target",
    async (e, process, start, end, workType = undefined, finishAt = undefined) => {
      return await getSpc(process, start, end, workType, finishAt);
    }
  );
  ipcMain.handle("spc-del-by-uuid", async (e, uuid) => {
    return await delByUuid(uuid);
  });
  ipcMain.handle("finish-batch", async () => {
    return await finishBatch();
  });
  ipcMain.handle("anomaly-insert", async (e, data) => {
    return await insertAnomaly(data);
  });
  ipcMain.handle("anomaly-get-target", async (e, workCenterId, process, start, end) => {
    return await getAnomaly(workCenterId, process, start, end);
  });
}
